
// advice.js
const express = require('express');
const bodyParser = require('body-parser')
const router = express.Router();
const cors = require('cors');


const mysql = require('mysql2')

require('dotenv').config();
const dbserver = process.env.DB_SERVER
const dbUser = process.env.DB_USER;
const dbPass = process.env.DB_PASSWORD;
const dbname= process.env.DB_DATABASE


const pool = mysql.createPool({
  connectionLimit: 10, // 连接池中最大的连接数量
  host: dbserver, // 数据库的地址
  user:dbUser, // 数据库的用户名
  password: dbPass, // 数据库的密码
  database: dbname // 数据库的名称
});


let metadata = {}

router.use(cors())
router.use(bodyParser.json());
router.use(bodyParser.urlencoded({ extended: true }));
router.use(bodyParser.text());

router.get('/get/categroySelect', (req, res) => {

  let params = req.query
  let condtions = ""
  let categroy = params.categroy

  console.log(params)

  let sqlSchool = "select * from metadata_tb where categroy = '"+ categroy+"'"

  if (params.key !== undefined && params.key !== "") {
    condtions = condtions + " and tagname like '%" + params.key + "%'"
  }
  sqlSchool = sqlSchool + condtions

  console.log(sqlSchool);

  pool.query(sqlSchool, (err, results, fiedds) => {
    if (err) {
      console.log(err)
      res.json({ "error": err, "code": '500', "msg": 'Query Erorr' })
      return;
    }
    res.json({ "data": results, "code": '200', "msg": 'Query success!' })
  })

})




//初始化条件数据
router.get('/get/condtionlist', async (req, res) => {
  let sqlSchool = "select * from metadata_tb where categroy = '学校'"
  let sqlMajor = "select * from metadata_tb where categroy = '专业'"
  let sqlDegree = "select * from metadata_tb where categroy = '最高学历'"
  let sqlExperience = "select * from metadata_tb where categroy = '工作经验'"
  
  const schools = queryMetaData(sqlSchool, 'schools')
  const majors = queryMetaData(sqlMajor, 'majors')
  const degrees = queryMetaData(sqlDegree, 'degrees')
  const experiences = queryMetaData(sqlExperience, 'experiences')

  await Promise.all([schools, majors, degrees, experiences]);
  res.json({ data: metadata })
});



async function queryMetaData(sql, type) {
  return new Promise((resolve, reject) => {
    pool.query(sql, (err, results) => {
      if (err) {
        reject()
      } else {
        if (type == "schools") {
          metadata.schools = results
        }
        if (type == "majors") {
          metadata.majors = results
        }
        if (type == "degrees") {
          metadata.degrees = results
        }
        if (type == "experiences") {
          metadata.experiences = results
        }
        resolve()
      }
    })
  })
}




router.get("/get/total", (req, res) => {
  const params = req.query
  let sql = "select count(1) as total from advice_tb "


  let conditionSql = ""
  if (params.queryString !== "") {
    conditionSql = conditionSql + " where content like   '%" + params.queryString + "%'"
  } else {
    conditionSql = conditionSql + " where content like '%%'"
  }
  if (params.degree !== "") {
    conditionSql = conditionSql + " and education = '" + params.degree + "' "
  }
  if (params.major !== "") {
    conditionSql = conditionSql + " and major = '" + params.major + "' "
  }
  if (params.school !== "" && params.school !== undefined) {
    conditionSql = conditionSql + " and school = '" + params.school + "' "
  }
  if (params.experience !== "") {
    conditionSql = conditionSql + " and work_exp = '" + params.experience + "' "
  }

  sql = sql + conditionSql

  console.log("#### sql get total :" + sql)
  pool.query(sql, (err, results, fiedds) => {
    if (err) {
      console.log(err)
      res.json({ "error": err, "code": '500', "msg": 'Query Erorr' })
      return;
    }
    res.json({ "data": results, "code": '200', "msg": 'Query success!' })
  })
}),


  //初始化条件数据
  router.get('/get/querySugest', (req, res) => {
    console.log(req.query)
    const params = req.query
    var pagesize = parseInt(req.query.pagesize) || 10;
    var page = parseInt(req.query.pageNum) || 1
    var offset = (page - 1) * pagesize

    var sql = "select * from advice_tb "

    let conditionSql = ""
    if (params.queryString !== "") {
      conditionSql = conditionSql + " where content like   '%" + params.queryString + "%'"
    } else {
      conditionSql = conditionSql + " where content like '%%'"
    }
    if (params.degree !== "") {
      conditionSql = conditionSql + " and education = '" + params.degree + "' "
    }
    if (params.major !== "") {
      conditionSql = conditionSql + " and major = '" + params.major + "' "
    }
    if (params.school !== "") {
      conditionSql = conditionSql + " and school = '" + params.school + "' "
    }
    if (params.experience !== "") {
      conditionSql = conditionSql + " and work_exp = '" + params.experience + "' "
    }

    var orderby = " order by create_time desc"
    var limit = ` limit  ${pagesize} offset ${offset}`
    sql = sql + conditionSql + orderby + limit

    console.log("SQL :" + sql)

    pool.query(sql, (err, results, fiedds) => {
      if (err) {
        console.log(err)
        res.json({ "error": err, "code": '500', "msg": 'Query Erorr' })
        return;
      }
      res.json({ "data": results, "code": '200', "msg": 'Query success!' })
    })
  });



//提交建议
router.post('/addAdvice', (req, res) => {
  console.log(req)
  const params = req.body;
  // 在控制台中打印接收到的数据
  console.log('Received data:', params);
  const { major, school, content, work_exp, degree } = req.body
  const create_time = new Date()

  var sql = 'insert into advice_tb (major,school,content,education,work_exp,create_time) values (?,?,?,?,?,?)'

  pool.query(sql, [major, school, content, degree, work_exp, create_time], (err, result, feilds) => {
    if (err) {
      console.log("insert into table error", err)
      res.json({ "error": err, "code": '500', "msg": 'Query Erorr' })
      return;
    }
    console.log("添加建议成功")
    res.json({ "text": '添加建议成功', "code": '200', "msg": 'INSERT ok' })
  })

});


module.exports = router;